﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.VisualBasic;

namespace InterestCalculationBiz
{
    public static class MicroLoan
    {
        public static double MonthlyFee
        {
            get;
            set;
        }
        public static double EstablishmentFee
        {
            get;
            set;
        }
        public static double TotalMonthlyFee
        {
            get;
            set;
        }
        public static DateTime LastPaymentDate
        {
            get;
            set;
        }

        public static DataTable aSACCCalculteByTerm(double loanAmount, int term, int repaymentOpt, double establishFee, double totMonthlyFee, DateTime settlementDate, DateTime firstPaymentDate)
        {
            DataTable dtSchedule = new DataTable();
            double firstPaymentMonthlyFee = GetFirstPaymentMonthlyFee(settlementDate, firstPaymentDate);
            double firstCharge = loanAmount + establishFee + firstPaymentMonthlyFee;
            decimal totLoanAmount = Convert.ToDecimal(loanAmount + establishFee + MicroLoan.TotalMonthlyFee);
            DateTime lastPaymentDate = new DateTime();
            int paymentTerm = GetPaymentTerm(settlementDate, firstPaymentDate, term, repaymentOpt, out lastPaymentDate);
            decimal instalmentAmt = Convert.ToDecimal(Math.Round(totLoanAmount / paymentTerm, 2));

            dtSchedule.Columns.Add("DueDate", typeof(DateTime));
            dtSchedule.Columns.Add("Repayment", typeof(decimal));
            dtSchedule.Columns.Add("Feecharge", typeof(decimal));
            dtSchedule.Columns.Add("Balance", typeof(decimal));

            if (totLoanAmount - instalmentAmt * paymentTerm > 0)
            {
                instalmentAmt = instalmentAmt + 0.01m;
            }

            decimal balanceAmt = Convert.ToDecimal(firstCharge);
            int skipMonth = Convert.ToInt16(firstPaymentMonthlyFee / MicroLoan.MonthlyFee);
            DateTime nextMonthlyFeeChargeDate = GetNextMonthlyFeeChargeDate(settlementDate, skipMonth);

            double feeCharge = 0;

            for (int count = 0; count < paymentTerm; count++)
            {
                feeCharge = 0;
                DataRow dr = dtSchedule.NewRow();
                DateTime nextPaymentDate = DateTime.Now;

                if (repaymentOpt == 30)
                {
                    nextPaymentDate = firstPaymentDate.AddMonths(count);
                }
                else
                {
                    nextPaymentDate = firstPaymentDate.AddDays(count * repaymentOpt);
                }

                if (nextPaymentDate >= nextMonthlyFeeChargeDate)
                {
                    skipMonth += 1;
                    balanceAmt += Convert.ToDecimal(MicroLoan.MonthlyFee);
                    feeCharge = MicroLoan.MonthlyFee;
                    DataRow monthlyFeeRow = dtSchedule.NewRow();
                    monthlyFeeRow["DueDate"] = nextMonthlyFeeChargeDate;
                    monthlyFeeRow["Feecharge"] = String.Format("{0:0.00}", feeCharge);
                    monthlyFeeRow["Balance"] = String.Format("{0:0.00}", balanceAmt);
                    nextMonthlyFeeChargeDate = GetNextMonthlyFeeChargeDate(settlementDate, skipMonth);

                    dtSchedule.Rows.Add(monthlyFeeRow);
                }

                if (balanceAmt - instalmentAmt < 0)
                {
                    instalmentAmt = balanceAmt;
                    balanceAmt = 0;
                }
                else
                {
                    balanceAmt = balanceAmt - instalmentAmt;
                }

                dr["DueDate"] = nextPaymentDate;
                dr["Repayment"] = String.Format("{0:0.00}", instalmentAmt);
                dr["Balance"] = String.Format("{0:0.00}", balanceAmt);

                dtSchedule.Rows.Add(dr);
            }

            return dtSchedule;
        }

        public static DataTable CalculateEMIV2(double principal, int noOfWeeks, int interestRate, DateTime loanDate, DateTime firstPaymentDate, int LoanType)
        {
            Double initialInstallment;
            //=IF(I1=B1,52.18,IF(I1=B2,26.09,12))
            double loanDuration = 0;
            double frequency = 0;
            double weekdays = 52.18;
            double fortnightly = 26.09;
            double monthly = 12;

            if (LoanType == 0)
            {
                loanDuration = noOfWeeks;
                frequency = weekdays;
            }
            else if (LoanType == 1)
            {
                loanDuration = noOfWeeks / 2;
                frequency = fortnightly;
            }
            else if (LoanType == 2)
            {
                loanDuration = noOfWeeks / 4;
                frequency = monthly;
            }

            //=IF(I1=B1,I3,IF(I1=B2,ROUNDUP(I3/2,0),ROUNDUP(I3*7/365*12,0)))
            //=SUMIF(H18:H213,"<="&I6,M18:M511)+N13*(MONTH(I6)-MONTH(I5)+1+(YEAR(I6)-YEAR(I5))*12)
            double preFirstRepaymentInterest = 0;
            //=ROUND(PMT(I4/D5,I11,-Q17-I7,,1),2)+N13*3            

            //PV  /  [(1- (1 / (1 + i)n )) / i] 
            //Where PV=amt of loan (present value) 
            //i=periodic interest rate 
            //n=# of periods
            //initialInstallment = principal/[(1-(1/(1+interestRate)loanDuration))/interestRate];
            DateTime chkFirstPaymentDate = DateAndTime.Now;
            double pmtRate = (double)interestRate / (double)100.00;
            pmtRate = (double)pmtRate / (double)frequency;
            initialInstallment = Math.Round(Financial.Pmt(pmtRate, frequency, -preFirstRepaymentInterest - principal, 0), 2);
            initialInstallment = 100.86;
            double rePayment = initialInstallment;            
            #region Intialize DataTable
            DataTable dtPeriodicPayment = new DataTable();
            dtPeriodicPayment.Columns.Add("Date", typeof(DateTime));
            dtPeriodicPayment.Columns.Add("Payment", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Charges", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Balance", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Interest", typeof(decimal));
            #endregion
       
            double lastInitialInstallment =0;
            double balance = 0;
            double x = 0;
               double y = 0;
             rePayment = initialInstallment;//Range("I9") = Range("q18")
            lastInitialInstallment = initialInstallment;//Last_inst = Range("q18")
        balance = balance; //Balance = Range("q19")
        x = rePayment/10;    //x = Range("i9") / 10
        y = x; //y = x

    while (balance > 0) //While Range("q19") > 0
    {
        //'Last_inst = Range("I9") + x
        rePayment =  rePayment+x;//Range("I9") = Range("I9") + x
        //'Balance = Range("q19")
    }

    double lineInterest = 0;
    double lineprincipal = principal;

    x = y; //x = y
    while (x >= 0.01) //While x >= 0.01
    {
        while (lineprincipal < 0.005) //While Range("q19") < 0.005
        {
            //'Balance = Range("q19")
            lastInitialInstallment = rePayment; //Last_inst = Range("I9")
            rePayment = lastInitialInstallment - x;//Range("i9") = (Last_inst - x)
            //'Diff = Range("q20")          
            #region AssignDataTable
            DataRow periodicBalance = dtPeriodicPayment.NewRow();
            periodicBalance["Date"] = firstPaymentDate; ;
            periodicBalance["Payment"] = initialInstallment;
            lineInterest = lineInterest + (double)(((lineprincipal * interestRate * 7) / 100) / 365);
            periodicBalance["Interest"] = (double)(((lineprincipal * interestRate * 7) / 100) / 365); ;            
            periodicBalance["Charges"] = (double)0.00;
            lineprincipal = lineprincipal - initialInstallment;
            periodicBalance["Balance"] = lineprincipal;

            dtPeriodicPayment.Rows.Add(periodicBalance);
            #endregion
        }

        rePayment = lastInitialInstallment; ///Range("i9") = Last_inst

        x = x / 3; //x = x / 3

    }
    
                
                
                
                       
           

               

              

        


               
               
             

            return dtPeriodicPayment;
        }


        public static KeyValuePair<double[], DataTable> CalculateEMIWeekly(double principal, int noOfWeeks, int interestRate, DateTime loanDate, DateTime firstPaymentDate, int LoanType)
        {
            Double initialInstallment = 0;
            //=IF(I1=B1,52.18,IF(I1=B2,26.09,12))
            double loanDuration = 0;
            double frequency = 0;
            double weekdays = 52.18;
            double fortnightly = 26.09;
            double monthly = 12;
            double preFirstRepaymentInterest = 0;
            double lineTotalInterest = 0;
            #region Intialize DataTable
            DataTable dtPeriodicPayment = new DataTable();
            dtPeriodicPayment.Columns.Add("Line", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Date", typeof(DateTime));
            dtPeriodicPayment.Columns.Add("Payment", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Charges", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Balance", typeof(decimal));
            dtPeriodicPayment.Columns.Add("Interest", typeof(decimal));
            #endregion
            try
            {
              
            

                if (LoanType == 0)
                {
                    loanDuration = noOfWeeks;
                    frequency = weekdays;
                }
                else if (LoanType == 1)
                {
                    loanDuration = noOfWeeks / 2;
                    frequency = fortnightly;
                }
                else if (LoanType == 2)
                {
                    loanDuration = noOfWeeks / 4;
                    frequency = monthly;
                }

                //=IF(I1=B1,I3,IF(I1=B2,ROUNDUP(I3/2,0),ROUNDUP(I3*7/365*12,0)))
                //=SUMIF(H18:H213,"<="&I6,M18:M511)+N13*(MONTH(I6)-MONTH(I5)+1+(YEAR(I6)-YEAR(I5))*12)
                
                //=ROUND(PMT(I4/D5,I11,-Q17-I7,,1),2)+N13*3            

                //PV  /  [(1- (1 / (1 + i)n )) / i] 
                //Where PV=amt of loan (present value) 
                //i=periodic interest rate 
                //n=# of periods            
                double value1 = 1 / (1 + interestRate);
                double value2 = (1 - value1 * noOfWeeks) / interestRate;
                initialInstallment = principal / value2;
                DateTime chkFirstPaymentDate = DateAndTime.Now;
                double pmtRate = (double)interestRate / 100;
                pmtRate = (double)pmtRate / 52.18;
                initialInstallment = Math.Round(Financial.Pmt((double)pmtRate, noOfWeeks, -preFirstRepaymentInterest - principal, 0), 2);
                //pmtRate = (double)interestRate / 100;
                //initialInstallment = Math.Round(principal / ((1 - (1 / Math.Pow(1 + (pmtRate / 52.18), noOfWeeks))) / (pmtRate / 52.18)), 2);
                //=1000/((1-(1/(1+ (10%/52.18)) ^ 52) )/(10%/52.18))
                double lineprincipal = principal;
                double lineInterest = 0;
                int lineMonth = firstPaymentDate.Month;
                
                //Add Advance Line
                #region AssignDataTable
                DataRow periodicBalance = dtPeriodicPayment.NewRow();
                periodicBalance["Line"] = 0;
                periodicBalance["Date"] = loanDate;
                periodicBalance["Payment"] = 0;
                periodicBalance["Interest"] = 0;
                periodicBalance["Charges"] = 0;
                periodicBalance["Balance"] = lineprincipal;
                dtPeriodicPayment.Rows.Add(periodicBalance);
                #endregion

                for (int count = 0; count < loanDuration; count++)
                {

                    #region AssignDataTable
                    periodicBalance = dtPeriodicPayment.NewRow();
                    periodicBalance["Line"] = count + 1;
                    periodicBalance["Date"] = firstPaymentDate;
                    periodicBalance["Payment"] = initialInstallment;

                    if (count == 0)
                    {
                        TimeSpan span = firstPaymentDate.Subtract(loanDate);
                        lineInterest = lineInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * span.Days) / 100) / 365), 2);
                        periodicBalance["Interest"] = Math.Round((double)((((double)lineprincipal * (double)interestRate * span.Days) / 100) / 365), 2);
                    }
                    else
                    {
                        lineInterest = lineInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                        periodicBalance["Interest"] = Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                    }
                    lineTotalInterest = lineTotalInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                    periodicBalance["Charges"] = (double)0.00;
                    lineprincipal = lineprincipal - initialInstallment;
                    periodicBalance["Balance"] = lineprincipal;

                    dtPeriodicPayment.Rows.Add(periodicBalance);
                    #endregion
                    if (LoanType == 0)
                        chkFirstPaymentDate = firstPaymentDate.AddDays(7);
                    else if (LoanType == 1)
                        chkFirstPaymentDate = firstPaymentDate.AddDays(14);
                    else if (LoanType == 2)
                        chkFirstPaymentDate = firstPaymentDate.AddMonths(1);
                    //New weekly Payment starts
                    if (lineMonth != chkFirstPaymentDate.Month)
                    {


                        lineMonth = firstPaymentDate.Month;
                        #region AssignDataTable
                        periodicBalance = dtPeriodicPayment.NewRow();
                        DateTime origDT = firstPaymentDate;
                        DateTime lastDate = new DateTime(origDT.Year, origDT.Month, 1).AddMonths(1).AddDays(-1);
                        periodicBalance["Line"] = 0;
                        periodicBalance["Date"] = lastDate;
                        periodicBalance["Payment"] = (double)0.00;
                        TimeSpan span = lastDate.Subtract(origDT);
                        int spanDayCount = 0;
                        spanDayCount = span.Days;
                        spanDayCount++;
                        lineInterest = lineInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * spanDayCount) / 100) / 365), 2);
                        periodicBalance["Interest"] = Math.Round((double)((((double)lineprincipal * (double)interestRate * spanDayCount) / 100) / 365), 2);
                        lineTotalInterest = lineTotalInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * spanDayCount) / 100) / 365), 2);
                        lineprincipal += lineInterest;
                        periodicBalance["Charges"] = Math.Round((double)lineInterest, 2);
                        periodicBalance["Balance"] = Math.Round(lineprincipal, 2);
                        dtPeriodicPayment.Rows.Add(periodicBalance);
                        //Reset lineInterest 
                        lineInterest = 0;
                        #endregion
                    }
                    firstPaymentDate = firstPaymentDate.AddDays(7);
                    lineMonth = firstPaymentDate.Month;
                    //Last Payment
                    if (count == loanDuration - 2)
                    {
                        count++;                        
                        #region AssignDataTable
                        periodicBalance = dtPeriodicPayment.NewRow();
                        periodicBalance["Line"] = count + 1;
                        periodicBalance["Date"] = firstPaymentDate;                        
                        lineInterest = lineInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                        periodicBalance["Interest"] = Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                        lineTotalInterest = lineTotalInterest + Math.Round((double)((((double)lineprincipal * (double)interestRate * 7) / 100) / 365), 2);
                        lineprincipal += lineInterest;
                        periodicBalance["Payment"] = Math.Round(lineprincipal); //initialInstallment;                    
                        periodicBalance["Charges"] = Math.Round((double)lineInterest, 2);
                        lineprincipal = lineprincipal - initialInstallment;
                        periodicBalance["Balance"] = 0;
                        dtPeriodicPayment.Rows.Add(periodicBalance);
                        #endregion


                    }
                }

            }
            catch (Exception ex)
            {
            }
            finally
            {
            
            }
            double[] strRepaymentAndInterest = { initialInstallment, lineTotalInterest };
            return new KeyValuePair<double[], DataTable>(strRepaymentAndInterest, dtPeriodicPayment);
        }
        
        /// <summary>
        /// Get next monthly fee charge date.
        /// </summary>
        /// <param name="currentChargeDate"></param>
        /// <param name="skipMonth"></param>
        /// <returns></returns>
        private static DateTime GetNextMonthlyFeeChargeDate(DateTime currentChargeDate, int skipMonth)
        {
            DateTime nextMonthChargeDate = currentChargeDate.AddMonths(skipMonth);
            int lastDayOfNextMonth = DateTime.DaysInMonth(nextMonthChargeDate.Year, nextMonthChargeDate.Month);

            if (currentChargeDate.Day > lastDayOfNextMonth)
            {
                DateTime monthAfter = nextMonthChargeDate.AddMonths(1);
                nextMonthChargeDate = new DateTime(monthAfter.Year, monthAfter.Month, 1, 0, 0, 0, monthAfter.Kind);
            }

            return nextMonthChargeDate;
        }
        /// <summary>
        /// Get monthly fee for first payment. Sometime could be more than 1.
        /// </summary>
        /// <param name="settlementDate"></param>
        /// <param name="firstPaymentDate"></param>
        /// <returns></returns>
        private static double GetFirstPaymentMonthlyFee(DateTime settlementDate, DateTime firstPaymentDate)
        {
            int monthDifference = (firstPaymentDate.Month - settlementDate.Month) + 12 * (firstPaymentDate.Year - settlementDate.Year);

            if (monthDifference == 0)
            {
                return MicroLoan.MonthlyFee;
            }
            else
            {
                if (firstPaymentDate.Day >= settlementDate.Day)
                {
                    monthDifference += 1;
                }
            }

            return MicroLoan.MonthlyFee * monthDifference;
        }
        /// <summary>
        /// Get establishment fee
        /// </summary>
        /// <param name="loanAmount"></param>
        /// <param name="establishFeePC"></param>
        /// <returns></returns>
        public static double GetTotalEstablishmentFee(double loanAmount, double establishFeePC)
        {
            MicroLoan.EstablishmentFee = Math.Round(loanAmount * (establishFeePC / 100), 2);
            return MicroLoan.EstablishmentFee;
        }
        /// <summary>
        /// Calculate how many monthes between first payment and last payment
        /// </summary>
        /// <param name="term"></param>
        /// <param name="repaymentOpt"></param>
        /// <param name="settlementDate"></param>
        /// <param name="firstPaymentDate"></param>
        /// <returns></returns>
        public static int GetPaymentTermsByMonth(int term, int repaymentOpt, DateTime settlementDate, DateTime firstPaymentDate)
        {
            try
            {
                DateTime lastPaymentDate = GetLastPaymentDate(settlementDate, firstPaymentDate, repaymentOpt, term);
                DateTime temp = settlementDate;
                int paymentTermsByMonth = 0;

                while (temp <= lastPaymentDate)
                {
                    paymentTermsByMonth += 1;
                    temp = temp.AddMonths(1);
                }

                return paymentTermsByMonth;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Get last payment date
        /// </summary>
        /// <param name="firstPaymentDate"></param>
        /// <param name="paymentTerm"></param>
        /// <param name="repaymentOpt"></param>
        /// <returns></returns>
        public static DateTime GetLastPaymentDate(DateTime settlementDate, DateTime firstPaymentDate, int repaymentOpt, int term)
        {
            try
            {
                DateTime lastPaymentDate = new DateTime();
                int paymentTerm = GetPaymentTerm(settlementDate, firstPaymentDate, term, repaymentOpt, out lastPaymentDate);
                return lastPaymentDate;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Calculate total monthly fee.
        /// </summary>
        /// <param name="loanAmount"></param>
        /// <param name="term"></param>
        /// <param name="repaymentOpt"></param>
        /// <param name="monthlyFeePC"></param>
        /// <param name="settlementDate"></param>
        /// <param name="firstPaymentDate"></param>
        /// <returns></returns>
        public static double GetTotalMonthlyFee(double loanAmount, int term, int repaymentOpt, double monthlyFeePC, DateTime settlementDate, DateTime firstPaymentDate)
        {
            MicroLoan.MonthlyFee = Math.Round((loanAmount * (monthlyFeePC / 100)), 2);
            int paymentTermsByMonth = GetPaymentTermsByMonth(term, repaymentOpt, settlementDate, firstPaymentDate);
            MicroLoan.TotalMonthlyFee = MicroLoan.MonthlyFee * paymentTermsByMonth;
            return MicroLoan.TotalMonthlyFee;
        }
        /// <summary>
        /// Calculate how many payments in contract
        /// </summary>
        /// <param name="settlementDate"></param>
        /// <param name="firstPaymentDate"></param>
        /// <param name="term"></param>
        /// <param name="repaymentOpt"></param>
        /// <returns></returns>
        private static int GetPaymentTerm(DateTime settlementDate, DateTime firstPaymentDate, int term, int repaymentOpt, out DateTime lastPaymentDate)
        {
            DateTime contractDueDate = GetContractDueDate(settlementDate, term);
            DateTime paymentDate = firstPaymentDate;
            int paymentTerm = 1;

            while (paymentDate < contractDueDate)
            {
                if ((contractDueDate.Date - paymentDate.Date).TotalDays < repaymentOpt)
                {
                    break;
                }
                else
                {
                    if (repaymentOpt == 30)
                    {
                        paymentDate = paymentDate.AddMonths(1);
                    }
                    else
                    {
                        paymentDate = paymentDate.AddDays(repaymentOpt);
                    }
                    paymentTerm = paymentTerm + 1;
                }
            }

            lastPaymentDate = paymentDate;

            if (paymentTerm > term)
            {
                paymentTerm = term;
            }
            return paymentTerm;
        }

        /// <summary>
        /// Get due date of contract.
        /// </summary>
        /// <param name="settlementDate"></param>
        /// <param name="term"></param>
        /// <param name="repaymentOpt"></param>
        /// <returns></returns>
        private static DateTime GetContractDueDate(DateTime settlementDate, int term)
        {
            return settlementDate.AddDays(term * 7); ;
        }
    }
}
